package main.java.com.chegg.coursepicker.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import com.mysql.jdbc.Driver;

/**
 *
 *
 *
 */
public class UserDAO {

    final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    final String DB_URL = "jdbc:mysql://live-dbpm.dev1.cloud.cheggnet.com/integration";
    final String USER = "tbflix";
    final String PASS = "c4tny2003";


    public List<Integer> getCourseIds(int userId) throws ClassNotFoundException, SQLException {
        String sql = "select course_id from cg_user_course where user_number = " + userId;

        Connection conn = getConnection();
        Statement statement = conn.createStatement();

        try {
            List<Integer> userNumbers = new ArrayList<Integer>();

            ResultSet resultSet = statement.executeQuery(sql);
            while(resultSet.next()){
                int user_number = resultSet.getInt("user_number");
                System.out.println("found user number " + user_number);
                userNumbers.add(user_number);
            }
            return userNumbers;
        } finally {
            conn.close();
            statement.close();
        }
    }


    public List<String> getCourseDesc(int userId) throws ClassNotFoundException, SQLException {
        String sql = "select course_code,course_desc" +
                " from `cg_course_master` cgm" +
                " inner join cg_user_course cus on cgm.`course_id` = cus.course_id" +
                " where cus.`user_number` = " + userId;

        Connection conn = getConnection();
        Statement statement = conn.createStatement();

        try {
            List<String> courseDescs = new ArrayList<String>();

            ResultSet resultSet = statement.executeQuery(sql);
            while(resultSet.next()){
                String course_code = resultSet.getString("course_code");
                String course_desc = resultSet.getString("course_desc");
                System.out.println("found course code " + course_code + " desc: " + course_desc);
                courseDescs.add(course_code + " " + course_desc);
            }
            return courseDescs;
        } finally {
            conn.close();
            statement.close();
        }
    }


    private Connection getConnection() throws ClassNotFoundException, SQLException {
        Connection conn = null;
        //STEP 2: Register JDBC driver
        Class.forName("com.mysql.jdbc.Driver");

        //STEP 3: Open a connection
        System.out.println("Connecting to database...");
        conn = (Connection) DriverManager.getConnection(DB_URL, USER, PASS);
        return conn;
    }
}
